import sqlite3

# 连接到SQLite数据库（如果不存在则会创建）
conn = sqlite3.connect('music_app.db')

# 创建游标对象
cursor = conn.cursor()

# 创建用户信息表
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                    user_id INTEGER PRIMARY KEY,
                    username TEXT NOT NULL,
                    email TEXT NOT NULL,
                    password TEXT NOT NULL
                )''')

# 创建歌曲信息表
cursor.execute('''CREATE TABLE IF NOT EXISTS songs (
                    song_id INTEGER PRIMARY KEY,
                    title TEXT NOT NULL,
                    artist TEXT NOT NULL,
                    file_path TEXT NOT NULL -- 添加 file_path 列
                )''')

# 创建播放列表表
cursor.execute('''CREATE TABLE IF NOT EXISTS playlists (
                    playlist_id INTEGER PRIMARY KEY,
                    user_id INTEGER,
                    song_id INTEGER,
                    FOREIGN KEY (user_id) REFERENCES users(user_id),
                    FOREIGN KEY (song_id) REFERENCES songs(song_id)
                )''')

# 插入示例数据
cursor.execute("INSERT INTO users (username, email, password) VALUES (?, ?, ?)", ('user1', 'user1@example.com', 'password1'))
cursor.execute("INSERT INTO users (username, email, password) VALUES (?, ?, ?)", ('user2', 'user2@example.com', 'password2'))
cursor.execute("INSERT INTO songs (title, artist, file_path) VALUES (?,?,?)", ('不分手的恋爱', '汪苏泷','song/不分手的恋爱.mp3'))
cursor.execute("INSERT INTO songs (title, artist, file_path) VALUES (?,?,?)", ('告白气球', '周杰伦','song/告白气球.mp3'))
cursor.execute("INSERT INTO songs (title, artist, file_path) VALUES (?,?,?)", ('平凡天使', '邓紫棋','song/平凡天使.mp3'))
cursor.execute("INSERT INTO playlists (user_id, song_id) VALUES (?, ?)", (1, 1))
cursor.execute("INSERT INTO playlists (user_id, song_id) VALUES (?, ?)", (1, 2))
cursor.execute("INSERT INTO playlists (user_id, song_id) VALUES (?, ?)", (2, 2))

# 提交事务
conn.commit()

# 关闭连接
conn.close()
